﻿using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using rock.UI.common;

namespace rock.DAL
{
	/// <summary>
	/// 数据访问类:DalFEE_OTHER
	/// </summary>
	public partial class DalFEE_OTHER
	{
		public DalFEE_OTHER()
		{}
		#region  Method

		/// <summary>
		/// 是否存在该记录
		/// </summary>
        public bool Exists(int SEQID)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select count(1) from FEE_OTHER");
			strSql.Append(" where SEQID=@SEQID ");
			SqlParameter[] parameters = {
					new SqlParameter("@SEQID", SqlDbType.Int,4)			};
			parameters[0].Value = SEQID;

			return DbHelperSQL.Exists(strSql.ToString(),parameters);
		}


		/// <summary>
		/// 增加一条数据
		/// </summary>
		public bool Add(rock.Model.ModelFEE_OTHER model)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("insert into FEE_OTHER(");
            strSql.Append("MONTH,FEE_NAME,HOUSE_SEQID,CUSTOM_SEQID,CONTRACT_SEQID,START_TIME,STOP_TIME,START_VALUE,STOP_VALUE,TOTAL_VALUE,FEE,TOTAL_FEE,FEE_STATUS,OPERA_MAN,OPERA_TIME,RECORD_TIME,NOTE,FEE_MODEL)");
			strSql.Append(" values (");
            strSql.Append("@MONTH,@FEE_NAME,@HOUSE_SEQID,@CUSTOM_SEQID,@CONTRACT_SEQID,@START_TIME,@STOP_TIME,@START_VALUE,@STOP_VALUE,@TOTAL_VALUE,@FEE,@TOTAL_FEE,@FEE_STATUS,@OPERA_MAN,@OPERA_TIME,@RECORD_TIME,@NOTE,@FEE_MODEL)");
			SqlParameter[] parameters = {
				 
					new SqlParameter("@MONTH", SqlDbType.VarChar,50),
					new SqlParameter("@FEE_NAME", SqlDbType.VarChar,50),
					new SqlParameter("@HOUSE_SEQID", SqlDbType.Int,4),
					new SqlParameter("@CUSTOM_SEQID", SqlDbType.Int,4),
					new SqlParameter("@CONTRACT_SEQID", SqlDbType.Int,4),
					new SqlParameter("@START_TIME", SqlDbType.DateTime),
					new SqlParameter("@STOP_TIME", SqlDbType.DateTime),
					new SqlParameter("@START_VALUE", SqlDbType.Decimal,9),
					new SqlParameter("@STOP_VALUE", SqlDbType.Decimal,9),
					new SqlParameter("@TOTAL_VALUE", SqlDbType.Decimal,9),
					new SqlParameter("@FEE", SqlDbType.Decimal,9),
					new SqlParameter("@TOTAL_FEE", SqlDbType.Decimal,9),
					new SqlParameter("@FEE_STATUS", SqlDbType.Int,4),
					new SqlParameter("@OPERA_MAN", SqlDbType.VarChar,30),
					new SqlParameter("@OPERA_TIME", SqlDbType.DateTime),
					new SqlParameter("@RECORD_TIME", SqlDbType.DateTime),
					new SqlParameter("@NOTE", SqlDbType.VarChar,300),
                    new SqlParameter("@FEE_MODEL", SqlDbType.Int,4)};
			
			parameters[0].Value = model.MONTH;
			parameters[1].Value = model.FEE_NAME;
			parameters[2].Value = model.HOUSE_SEQID;
			parameters[3].Value = model.CUSTOM_SEQID;
			parameters[4].Value = model.CONTRACT_SEQID;
			parameters[5].Value = model.START_TIME;
			parameters[6].Value = model.STOP_TIME;
			parameters[7].Value = model.START_VALUE;
			parameters[8].Value = model.STOP_VALUE;
			parameters[9].Value = model.TOTAL_VALUE;
			parameters[10].Value = model.FEE;
			parameters[11].Value = model.TOTAL_FEE;
			parameters[12].Value = model.FEE_STATUS;
			parameters[13].Value = model.OPERA_MAN;
			parameters[14].Value = model.OPERA_TIME;
			parameters[15].Value = model.RECORD_TIME;
			parameters[16].Value = model.NOTE;
            parameters[17].Value = model.FEE_MODEL;

			int rows=DbHelperSQL.ExecuteSql(strSql.ToString(),parameters);
			if (rows > 0)
			{
				return true;
			}
			else
			{
				return false;
			}
		}
		/// <summary>
		/// 更新一条数据
		/// </summary>
		public bool Update(rock.Model.ModelFEE_OTHER model)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("update FEE_OTHER set ");
			strSql.Append("MONTH=@MONTH,");
			strSql.Append("FEE_NAME=@FEE_NAME,");
			strSql.Append("HOUSE_SEQID=@HOUSE_SEQID,");
			strSql.Append("CUSTOM_SEQID=@CUSTOM_SEQID,");
			strSql.Append("CONTRACT_SEQID=@CONTRACT_SEQID,");
			strSql.Append("START_TIME=@START_TIME,");
			strSql.Append("STOP_TIME=@STOP_TIME,");
			strSql.Append("START_VALUE=@START_VALUE,");
			strSql.Append("STOP_VALUE=@STOP_VALUE,");
			strSql.Append("TOTAL_VALUE=@TOTAL_VALUE,");
			strSql.Append("FEE=@FEE,");
			strSql.Append("TOTAL_FEE=@TOTAL_FEE,");
			strSql.Append("FEE_STATUS=@FEE_STATUS,");
			strSql.Append("OPERA_MAN=@OPERA_MAN,");
			strSql.Append("OPERA_TIME=@OPERA_TIME,");
			strSql.Append("RECORD_TIME=@RECORD_TIME,");
			strSql.Append("NOTE=@NOTE,");
            strSql.Append("FEE_MODEL=@FEE_MODEL");
            
			strSql.Append(" where SEQID=@SEQID ");
			SqlParameter[] parameters = {
					new SqlParameter("@MONTH", SqlDbType.Char,10),
					new SqlParameter("@FEE_NAME", SqlDbType.Char,50),
					new SqlParameter("@HOUSE_SEQID", SqlDbType.Int,4),
					new SqlParameter("@CUSTOM_SEQID", SqlDbType.Int,4),
					new SqlParameter("@CONTRACT_SEQID", SqlDbType.Int,4),
					new SqlParameter("@START_TIME", SqlDbType.DateTime),
					new SqlParameter("@STOP_TIME", SqlDbType.DateTime),
					new SqlParameter("@START_VALUE", SqlDbType.Decimal,9),
					new SqlParameter("@STOP_VALUE", SqlDbType.Decimal,9),
					new SqlParameter("@TOTAL_VALUE", SqlDbType.Decimal,9),
					new SqlParameter("@FEE", SqlDbType.Decimal,9),
					new SqlParameter("@TOTAL_FEE", SqlDbType.Decimal,9),
					new SqlParameter("@FEE_STATUS", SqlDbType.Int,4),
					new SqlParameter("@OPERA_MAN", SqlDbType.VarChar,30),
					new SqlParameter("@OPERA_TIME", SqlDbType.DateTime),
					new SqlParameter("@RECORD_TIME", SqlDbType.DateTime),
					new SqlParameter("@NOTE", SqlDbType.VarChar,300),
                    new SqlParameter("@FEE_MODEL", SqlDbType.Int,4),
					new SqlParameter("@SEQID", SqlDbType.Int,4)};
			parameters[0].Value = model.MONTH;
			parameters[1].Value = model.FEE_NAME;
			parameters[2].Value = model.HOUSE_SEQID;
			parameters[3].Value = model.CUSTOM_SEQID;
			parameters[4].Value = model.CONTRACT_SEQID;
			parameters[5].Value = model.START_TIME;
			parameters[6].Value = model.STOP_TIME;
			parameters[7].Value = model.START_VALUE;
			parameters[8].Value = model.STOP_VALUE;
			parameters[9].Value = model.TOTAL_VALUE;
			parameters[10].Value = model.FEE;
			parameters[11].Value = model.TOTAL_FEE;
			parameters[12].Value = model.FEE_STATUS;
			parameters[13].Value = model.OPERA_MAN;
			parameters[14].Value = model.OPERA_TIME;
			parameters[15].Value = model.RECORD_TIME;
			parameters[16].Value = model.NOTE;
            parameters[17].Value = model.FEE_MODEL;
			parameters[18].Value = model.SEQID;

			int rows=DbHelperSQL.ExecuteSql(strSql.ToString(),parameters);
			if (rows > 0)
			{
				return true;
			}
			else
			{
				return false;
			}
		}

		/// <summary>
		/// 删除一条数据
		/// </summary>
		public bool Delete(int SEQID)
		{
			
			StringBuilder strSql=new StringBuilder();
			strSql.Append("delete from FEE_OTHER ");
			strSql.Append(" where SEQID=@SEQID ");
			SqlParameter[] parameters = {
					new SqlParameter("@SEQID", SqlDbType.Int,4)			};
			parameters[0].Value = SEQID;

			int rows=DbHelperSQL.ExecuteSql(strSql.ToString(),parameters);
			if (rows > 0)
			{
				return true;
			}
			else
			{
				return false;
			}
		}
		/// <summary>
		/// 批量删除数据
		/// </summary>
		public bool DeleteList(string SEQIDlist )
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("delete from FEE_OTHER ");
			strSql.Append(" where SEQID in ("+SEQIDlist + ")  ");
			int rows=DbHelperSQL.ExecuteSql(strSql.ToString());
			if (rows > 0)
			{
				return true;
			}
			else
			{
				return false;
			}
		}


		/// <summary>
		/// 得到一个对象实体
		/// </summary>
		public rock.Model.ModelFEE_OTHER GetModel(int SEQID)
		{
			
			StringBuilder strSql=new StringBuilder();
            strSql.Append("select  top 1 SEQID,MONTH,FEE_NAME,HOUSE_SEQID,CUSTOM_SEQID,CONTRACT_SEQID,START_TIME,STOP_TIME,START_VALUE,STOP_VALUE,TOTAL_VALUE,FEE,TOTAL_FEE,FEE_STATUS,OPERA_MAN,OPERA_TIME,RECORD_TIME,NOTE,FEE_MODEL from FEE_OTHER ");
			strSql.Append(" where SEQID=@SEQID ");
			SqlParameter[] parameters = {
					new SqlParameter("@SEQID", SqlDbType.Int,4)			};
			parameters[0].Value = SEQID;

			rock.Model.ModelFEE_OTHER model=new rock.Model.ModelFEE_OTHER();
			DataSet ds=DbHelperSQL.Query(strSql.ToString(),parameters);
			if(ds.Tables[0].Rows.Count>0)
			{
				if(ds.Tables[0].Rows[0]["SEQID"]!=null && ds.Tables[0].Rows[0]["SEQID"].ToString()!="")
				{
					model.SEQID=int.Parse(ds.Tables[0].Rows[0]["SEQID"].ToString());
				}
				if(ds.Tables[0].Rows[0]["MONTH"]!=null && ds.Tables[0].Rows[0]["MONTH"].ToString()!="")
				{
					model.MONTH=ds.Tables[0].Rows[0]["MONTH"].ToString();
				}
				if(ds.Tables[0].Rows[0]["FEE_NAME"]!=null && ds.Tables[0].Rows[0]["FEE_NAME"].ToString()!="")
				{
					model.FEE_NAME=ds.Tables[0].Rows[0]["FEE_NAME"].ToString();
				}
				if(ds.Tables[0].Rows[0]["HOUSE_SEQID"]!=null && ds.Tables[0].Rows[0]["HOUSE_SEQID"].ToString()!="")
				{
					model.HOUSE_SEQID=int.Parse(ds.Tables[0].Rows[0]["HOUSE_SEQID"].ToString());
				}
				if(ds.Tables[0].Rows[0]["CUSTOM_SEQID"]!=null && ds.Tables[0].Rows[0]["CUSTOM_SEQID"].ToString()!="")
				{
					model.CUSTOM_SEQID=int.Parse(ds.Tables[0].Rows[0]["CUSTOM_SEQID"].ToString());
				}
				if(ds.Tables[0].Rows[0]["CONTRACT_SEQID"]!=null && ds.Tables[0].Rows[0]["CONTRACT_SEQID"].ToString()!="")
				{
					model.CONTRACT_SEQID=int.Parse(ds.Tables[0].Rows[0]["CONTRACT_SEQID"].ToString());
				}
				if(ds.Tables[0].Rows[0]["START_TIME"]!=null && ds.Tables[0].Rows[0]["START_TIME"].ToString()!="")
				{
					model.START_TIME=DateTime.Parse(ds.Tables[0].Rows[0]["START_TIME"].ToString());
				}
				if(ds.Tables[0].Rows[0]["STOP_TIME"]!=null && ds.Tables[0].Rows[0]["STOP_TIME"].ToString()!="")
				{
					model.STOP_TIME=DateTime.Parse(ds.Tables[0].Rows[0]["STOP_TIME"].ToString());
				}
				if(ds.Tables[0].Rows[0]["START_VALUE"]!=null && ds.Tables[0].Rows[0]["START_VALUE"].ToString()!="")
				{
					model.START_VALUE=decimal.Parse(ds.Tables[0].Rows[0]["START_VALUE"].ToString());
				}
				if(ds.Tables[0].Rows[0]["STOP_VALUE"]!=null && ds.Tables[0].Rows[0]["STOP_VALUE"].ToString()!="")
				{
					model.STOP_VALUE=decimal.Parse(ds.Tables[0].Rows[0]["STOP_VALUE"].ToString());
				}
				if(ds.Tables[0].Rows[0]["TOTAL_VALUE"]!=null && ds.Tables[0].Rows[0]["TOTAL_VALUE"].ToString()!="")
				{
                    model.TOTAL_VALUE = Decimal.Parse(ds.Tables[0].Rows[0]["TOTAL_VALUE"].ToString());
				}
				if(ds.Tables[0].Rows[0]["FEE"]!=null && ds.Tables[0].Rows[0]["FEE"].ToString()!="")
				{
					model.FEE=decimal.Parse(ds.Tables[0].Rows[0]["FEE"].ToString());
				}
				if(ds.Tables[0].Rows[0]["TOTAL_FEE"]!=null && ds.Tables[0].Rows[0]["TOTAL_FEE"].ToString()!="")
				{
					model.TOTAL_FEE=decimal.Parse(ds.Tables[0].Rows[0]["TOTAL_FEE"].ToString());
				}
				if(ds.Tables[0].Rows[0]["FEE_STATUS"]!=null && ds.Tables[0].Rows[0]["FEE_STATUS"].ToString()!="")
				{
					model.FEE_STATUS=int.Parse(ds.Tables[0].Rows[0]["FEE_STATUS"].ToString());
				}
				if(ds.Tables[0].Rows[0]["OPERA_MAN"]!=null && ds.Tables[0].Rows[0]["OPERA_MAN"].ToString()!="")
				{
					model.OPERA_MAN=ds.Tables[0].Rows[0]["OPERA_MAN"].ToString();
				}
				if(ds.Tables[0].Rows[0]["OPERA_TIME"]!=null && ds.Tables[0].Rows[0]["OPERA_TIME"].ToString()!="")
				{
					model.OPERA_TIME=DateTime.Parse(ds.Tables[0].Rows[0]["OPERA_TIME"].ToString());
				}
				if(ds.Tables[0].Rows[0]["RECORD_TIME"]!=null && ds.Tables[0].Rows[0]["RECORD_TIME"].ToString()!="")
				{
					model.RECORD_TIME=DateTime.Parse(ds.Tables[0].Rows[0]["RECORD_TIME"].ToString());
				}
				if(ds.Tables[0].Rows[0]["NOTE"]!=null && ds.Tables[0].Rows[0]["NOTE"].ToString()!="")
				{
					model.NOTE=ds.Tables[0].Rows[0]["NOTE"].ToString();
				}
                if (ds.Tables[0].Rows[0]["FEE_MODEL"] != null && ds.Tables[0].Rows[0]["FEE_MODEL"].ToString() != "")
				{
                    model.FEE_MODEL = int.Parse(ds.Tables[0].Rows[0]["FEE_MODEL"].ToString());
				}
                
				return model;
			}
			else
			{
				return null;
			}
		}

		/// <summary>
		/// 获得数据列表
		/// </summary>
		public DataSet GetList(string strWhere)
		{
			StringBuilder strSql=new StringBuilder();
            strSql.Append("select SEQID,MONTH,FEE_NAME,HOUSE_SEQID,CUSTOM_SEQID,CONTRACT_SEQID,START_TIME,STOP_TIME,START_VALUE,STOP_VALUE,TOTAL_VALUE,FEE,TOTAL_FEE,FEE_STATUS,OPERA_MAN,OPERA_TIME,RECORD_TIME,NOTE,FEE_MODEL ");
			strSql.Append(" FROM FEE_OTHER ");
			if(strWhere.Trim()!="")
			{
				strSql.Append(" where "+strWhere);
			}
			return DbHelperSQL.Query(strSql.ToString());
		}

		/// <summary>
		/// 获得前几行数据
		/// </summary>
		public DataSet GetList(int Top,string strWhere,string filedOrder)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select ");
			if(Top>0)
			{
				strSql.Append(" top "+Top.ToString());
			}
            strSql.Append(" SEQID,MONTH,FEE_NAME,HOUSE_SEQID,CUSTOM_SEQID,CONTRACT_SEQID,START_TIME,STOP_TIME,START_VALUE,STOP_VALUE,TOTAL_VALUE,FEE,TOTAL_FEE,FEE_STATUS,OPERA_MAN,OPERA_TIME,RECORD_TIME,NOTE,FEE_MODEL ");
			strSql.Append(" FROM FEE_OTHER ");
			if(strWhere.Trim()!="")
			{
				strSql.Append(" where "+strWhere);
			}
			strSql.Append(" order by " + filedOrder);
			return DbHelperSQL.Query(strSql.ToString());
		}


        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public PageDataSet GetListByPage(String where, string orderby, int pageNo, int pageSize)
        {
            String sql = @"  SELECT 
    FEE_OTHER.*  ,                      
	TEMP.XIAOQU_NAME ,
	TEMP.DONG_NAME,
	HOUSE_ROOM.HOUSE_NUMBER,
	HOUSE_ROOM.HOUSE_NUMBER AS HOUSE_NAME,
	RENT_CUSTOM.NAME AS CUSTOM_NAME,
	RENT_CONTRACT.CONTRACT_NUMBER
	FROM FEE_OTHER 
	LEFT JOIN HOUSE_ROOM   ON FEE_OTHER.HOUSE_SEQID= HOUSE_ROOM.SEQID  
	LEFT JOIN  
	(
	SELECT  HOUSE_XIAOQU.NAME as XIAOQU_NAME , HOUSE_DONG.NAME AS DONG_NAME, ALL_FLOOR_START,ALL_FLOOR_END , HAS_DIANTI ,
	HOUSE_XIAOQU.SEQID  as XIAOQU_SEQID , HOUSE_DONG.SEQID  as DONG_SEQID 
	FROM HOUSE_XIAOQU , HOUSE_DONG WHERE HOUSE_XIAOQU.SEQID = HOUSE_DONG.HOUSE_XIAOQU_SEQID
	) AS TEMP ON HOUSE_ROOM.HOUSE_DONG_SEQID = TEMP.DONG_SEQID 
	LEFT JOIN RENT_CUSTOM   ON FEE_OTHER.CUSTOM_SEQID = RENT_CUSTOM.SEQID  
	LEFT JOIN RENT_CONTRACT ON FEE_OTHER.CONTRACT_SEQID = RENT_CONTRACT.SEQID    ";
            if (where != null && !where.Equals(""))
            {
                sql += " WHERE " + where;
            }

            PageDataSet pds = new PageDataSet();
            pds.AllRecord = DbHelperSQL.GetRecordCount(sql);
            pds.DataSet = DbHelperSQL.GetListByPage(sql, orderby, pageNo, pageSize);

            return pds;

        }
        public DataSet getLastRecordByContract(int contract_seqid , string feeName )
        {
            String sql = @"
                SELECT FEE_OTHER.MONTH AS YYYYMM  , STOP_TIME , DATEADD(MONTH,1,STOP_TIME) AS NEXT_STOP ,STOP_VALUE FROM FEE_OTHER 
                WHERE 
	                CONTRACT_SEQID= {0}
                AND FEE_STATUS = {1}
                AND FEE_NAME = '{2}'";

            return DbHelperSQL.Query(String.Format(sql, contract_seqid, Constant.FEE_ROOM_YISHAO, feeName));
        }

		/*
		/// <summary>
		/// 分页获取数据列表
		/// </summary>
		public DataSet GetList(int PageSize,int PageIndex,string strWhere)
		{
			SqlParameter[] parameters = {
					new SqlParameter("@tblName", SqlDbType.VarChar, 255),
					new SqlParameter("@fldName", SqlDbType.VarChar, 255),
					new SqlParameter("@PageSize", SqlDbType.Int),
					new SqlParameter("@PageIndex", SqlDbType.Int),
					new SqlParameter("@IsReCount", SqlDbType.Bit),
					new SqlParameter("@OrderType", SqlDbType.Bit),
					new SqlParameter("@strWhere", SqlDbType.VarChar,1000),
					};
			parameters[0].Value = "FEE_OTHER";
			parameters[1].Value = "SEQID";
			parameters[2].Value = PageSize;
			parameters[3].Value = PageIndex;
			parameters[4].Value = 0;
			parameters[5].Value = 0;
			parameters[6].Value = strWhere;	
			return DbHelperSQL.RunProcedure("UP_GetRecordByPage",parameters,"ds");
		}*/

		#endregion  Method

       
    }
}

